<?php

namespace Wgx\PdoEx;


final class QueryBuilder
{
    const SqlDateTimeFormat = 'Y-m-d H:i:s';

    const LOGICAL_AND = '__AND__';
    const LOGICAL_OR = '__OR__';
    const RAW_WHERE = '__RAW_WHERE__';

    private static $name2QbArr = array();

    private $rwConnName;
    private $rPdo;
    private $rwPdo;

    private $columnString;

    /**
     * @param null|string $rwConnName - 主库连接名, 不传使用默认连接名
     * @return null|QueryBuilder
     */
    public static function useDb($rwConnName = null)
    {
        if (empty($rwConnName)) {
            $rwConnName = PdoManager::getSingleObj()->getDefaultName();
            if (empty($rwConnName)) {
                return null;
            }
        }

        if (empty(self::$name2QbArr[$rwConnName])) {
            self::$name2QbArr[$rwConnName] = new QueryBuilder($rwConnName);
        }

        return self::$name2QbArr[$rwConnName];
    }

    /**
     * 将 $columns 中的 column 替换成 $column2quotedMap 中 quoted 的形式(如果 $column2quotedMap 存在 column 的 quoted 的形式)
     * 如果 $column2quotedMap 不存在 column 的 quoted 的形式，则原封不动的返回 column
     * 例如: quoteColumn(['id', 'name', 'date'], ['id' => '`id`', 'date' => '`date`'])
     * => ['`id`', 'name', '`date`']
     * @param string[] $columns
     * @param array $column2quotedMap
     * @return array|mixed
     */
    public static function quoteColumn($columns, $column2quotedMap)
    {
        if (empty($column2quotedMap) || empty($columns) || !is_array($columns)) {
            return $columns;
        }

        $ret = array();
        foreach ($columns as $column) {
            if (empty($column2quotedMap[$column])) {
                $ret[] = $column;
            } else {
                $ret[] = $column2quotedMap[$column];
            }
        }
        return $ret;
    }

    /**
     * 将 $kv 中的 键 替换成 $column2quotedMap 中 quoted 的形式(如果 $column2quotedMap 存在 键 的 quoted 的形式)
     * 如果 $column2quotedMap 不存在 键 的 quoted 的形式，则原封不动的返回 键
     * 例如: quoteColumnK(['id' => 1, 'name' => '汪国新', 'date' => '2023-03-23'], ['id' => '`id`', 'date' => '`date`'])
     * => ['`id`' => 1, 'name' => '汪国新', '`date`' => '2023-03-23']
     * @param array $kv
     * @param array $column2quotedMap
     * @return array|mixed
     */
    public static function quoteColumnK($kv, $column2quotedMap)
    {
        if (empty($column2quotedMap) || empty($kv) || !is_array($kv)) {
            return $kv;
        }

        $ret = array();
        foreach ($kv as $k => $v) {
            if (empty($column2quotedMap[$k])) {
                $ret[$k] = $v;
            } else {
                $ret[$column2quotedMap[$k]] = $v;
            }
        }
        return $ret;
    }

    /**
     * QueryBuilder constructor.
     * @param null|string $rwConnName - 主库连接名, 不传使用默认连接名
     */
    private function __construct($rwConnName = null)
    {
        $this->rwConnName = $rwConnName;
    }

    /**
     * @return null|string - 主库连接名
     */
    public function getRwConnName()
    {
        return $this->rwConnName;
    }

    /**
     * 获取 特定用于读 或 特定用于读写 的 pdo
     * @param string $sqlMode - SqlModeEnum
     * @return null|\PDO
     */
    public function getPdo($sqlMode = SqlModeEnum::READ_WRITE)
    {
        return PdoManager::getSingleObj()->getPdo($this->rwConnName, $sqlMode);
    }

    /**
     * 获取 特定用于读 的 pdo
     * @return null|\PDO
     */
    public function getRPdo()
    {
        if (empty($this->rPdo)) {
            $this->rPdo = PdoManager::getSingleObj()->getPdo($this->rwConnName, SqlModeEnum::READ);
        }
        return $this->rPdo;
    }

    /**
     * 获取 特定用于读写 的 pdo
     * @return null|\PDO
     */
    public function getRwPdo()
    {
        if (empty($this->rwPdo)) {
            $this->rwPdo = PdoManager::getSingleObj()->getPdo($this->rwConnName, SqlModeEnum::READ_WRITE);
        }
        return $this->rwPdo;
    }

    /**
     * @return null|string
     */
    public function getColumnString()
    {
        return $this->columnString;
    }

    /**
     * 从 $columns 中提取出 有效的 $columns 字符串
     * @param string|string[] $columns - '*' 或者 'col1,col2,...' 或者 ['col1', 'col2', ...], 支持函数和合集函数, 例如 'SUBSTR(name, 1, 1) AS surname, col2, ...' 或者 ['col1', 'SUM(amount) AS balance', ...]
     * @return string
     */
    public function validColumnString($columns)
    {
        if (empty($columns)) {
            return '';
        }

        if (is_array($columns)) {
            $str = array();
            foreach ($columns as $column) {
                $column = trim(strval($column));
                if ($column) {
                    $str[] = $column;
                }
            }
            if (empty($str)) {
                return '';
            } else {
                return implode(', ', $str);
            }
        } else {
            return trim(strval($columns));
        }
    }

    /**
     * return $this->getRPdo()->query(...$args);
     * 使用 rPdo->query 来执行 sql, 所以在拼接 sql 的过程中也要使用 rPdo->quote 来转义参数字符串($this->getRPdo()->quote)
     *
     * 同 PDO::query 一样, 所以文档也请参看 PDO::query 的文档。
     * Prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object.
     *
     * public PDO::query(string $query, ?int $fetchMode = null): PDOStatement|false
     * public PDO::query(string $query, ?int $fetchMode = PDO::FETCH_COLUMN, int $colno): PDOStatement|false
     * public PDO::query(string $query, ?int $fetchMode = PDO::FETCH_CLASS, string $classname, array $constructorArgs): PDOStatement|false
     * public PDO::query(string $query, ?int $fetchMode = PDO::FETCH_INTO, object $object): PDOStatement|false
     *
     * @param ...$args
     * @return false|\PDOStatement - Returns a PDOStatement object or false on failure.
     */
    public function query(...$args)
    {
        if (empty($args[0])) {
            return false;
        }
        return $this->getRPdo()->query(...$args);
    }

    /**
     * return $this->getRwPdo()->exec($statement);
     * 使用 rwPdo->exec 来执行 sql, 所以在拼接 sql 的过程中也要使用 rwPdo->quote 来转义参数字符串($this->getRwPdo()->quote)
     *
     * 同 PDO::exec 一样, 所以文档也请参看 PDO::exec 的文档。
     * Executes an SQL statement in a single function call, returning the number of rows affected by the statement.
     *
     * Warning :
     * This function may return Boolean false, but may also return a non-Boolean value which evaluates to false.
     * Please read the section on Booleans for more information.
     * Use the === operator for testing the return value of this function.
     *
     * @param string $statement - The SQL statement to prepare and execute.
     * @return false|int - Returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, exec() returns 0.
     */
    public function exec($statement)
    {
        if (empty($statement)) {
            return false;
        }
        return $this->getRwPdo()->exec($statement);
    }

    // prepare
    // 增删改查 的 sql 模版语句都可以被 prepare,
    // 由于 pdoex 被设计成支持 读写分离,
    // 所以要求 使用 rPdo->prepare 来 prepare 查 类型的 sql 模版语句,
    // 使用 rwPdo->prepare 来 prepare 增删改 类型的 sql 模版语句.
    // 在使用 prepare 时, 不建议将 参数 拼接到 sql 模版语句中, 也就不需要转义参数字符串.
    // 如果确实需要将 参数 拼接到 sql 模版语句中,
    // 那么使用 rPdo->prepare 时就应该使用 rPdo->quote 来转义参数字符串($this->getRPdo()->quote),
    // 使用 rwPdo->prepare 时就应该使用 rwPdo->quote 来转义参数字符串($this->getRwPdo()->quote).

    // quote
    // sql 语句被哪个 pdo 实例执行, 在拼接 sql 的过程中就要使用 那个 pdo 实例的 quote 来转义参数字符串.
    // 如果 sql 语句被 rPdo 执行, 在拼接 sql 的过程中也要使用 rPdo->quote 来转义参数字符串($this->getRPdo()->quote).
    // 如果 sql 语句被 rwPdo 执行, 在拼接 sql 的过程中也要使用 rwPdo->quote 来转义参数字符串($this->getRwPdo()->quote)

    /**
     * return $this->getRwPdo()->beginTransaction();
     * 使用 rwPdo->beginTransaction() 来开启事务
     * @return bool - 失败返回 false, 成功返回 true
     */
    public function beginTransaction()
    {
        return $this->getRwPdo()->beginTransaction();
    }

    /**
     * return $this->getRwPdo()->commit();
     * 使用 rwPdo->commit() 来提交事务
     * @return bool - 失败返回 false, 成功返回 true
     */
    public function commit()
    {
        return $this->getRwPdo()->commit();
    }

    /**
     * return $this->getRwPdo()->rollBack();
     * 使用 rwPdo->rollBack() 来回滚事务
     * @return bool - 失败返回 false, 成功返回 true
     */
    public function rollBack()
    {
        return $this->getRwPdo()->rollBack();
    }

    /**
     * 使用 rwPdo($this->getRwPdo()) 来执行 INSERT
     *
     * @param string $table - 表名
     * @param array $data - ['col1'=>val1, 'col2'=>val2, ...]
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function insert($table, $data)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }
        if (empty($data)) {
            return null;
        }

        if (!is_array($data)) {
            throw new \InvalidArgumentException('参数传递错误');
        }

        $columns = array();
        $sqlParams = array();
        foreach ($data as $column => $value) {
            $column = trim(strval($column));
            if (empty($column)) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            $columns[] = $column;
            $sqlParams[] = $value;
        }

        $sql = "INSERT INTO {$table} (" . implode(', ', $columns) . ") VALUES (" . implode(',', array_fill(0, count($sqlParams), '?')) . ")";

        return new QueryBuilderResult($this->getRwPdo(), $sql, $sqlParams);
    }

    /**
     * 使用 rwPdo($this->getRwPdo()) 来执行 INSERT
     *
     * @param string $table - 表名
     * @param string[] $columns - ['col1', 'col2', ...]
     * @param array $data - object array, [[val11, val12, ...], [val21, val22, ...], ...]
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function insertMultiValue($table, $columns, $data)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }
        if (empty($columns)) {
            return null;
        }
        if (empty($data)) {
            return null;
        }

        if (!is_array($columns)) {
            throw new \InvalidArgumentException('参数传递错误');
        }
        if (!is_array($data)) {
            throw new \InvalidArgumentException('参数传递错误');
        }

        $columns = array_map(function ($column) {
            return trim(strval($column));
        }, $columns);
        $columnsCnt = count($columns);
        $dataTuple = '(' . implode(',', array_fill(0, $columnsCnt, '?')) . ')';

        $sqlParams = array();
        $sql = "INSERT INTO {$table} (" . implode(', ', $columns) . ')';
        $oxo = ' VALUES ';
        foreach ($data as $line) {
            if (empty($line) || !is_array($line) || count($line) < $columnsCnt) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            $sql .= "{$oxo}{$dataTuple}";

            $line = array_values($line);
            for ($i = 0; $i < $columnsCnt; $i++) {
                $sqlParams[] = $line[$i];
            }
            $oxo = ', ';
        }

        return new QueryBuilderResult($this->getRwPdo(), $sql, $sqlParams);
    }

    /**
     * 使用 rwPdo($this->getRwPdo()) 来执行 INSERT
     *
     * @param string $table - 表名
     * @param array $data - ['col1'=>val1, 'col2'=>val2, ...]
     * @param string[] $uniqueKeys - ['col1', 'col2', ...] , 导致 INSERT 冲突的 UNIQUE KEY, UPDATE 时不更新这些字段
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function insertOrUpdate($table, $data, $uniqueKeys)
    {
        if (empty($uniqueKeys)) {
            return null;
        }
        if (!is_array($uniqueKeys)) {
            throw new \InvalidArgumentException('参数传递错误');
        }
        $uniqueKeys = array_map(function ($item) {
            return trim(strval($item));
        }, $uniqueKeys);

        $qbResult = $this->insert($table, $data);
        if (empty($qbResult)) {
            return null;
        }
        $sql = trim($qbResult->getSql());
        $sqlParams = $qbResult->getSqlParams();

        $oxo = ' ON DUPLICATE KEY UPDATE ';
        foreach ($data as $column => $value) {
            $column = trim(strval($column));
            if (in_array($column, $uniqueKeys)) {
                continue;
            }
            $sql .= "{$oxo}{$column} = VALUES({$column})";
            $oxo = ', ';
        }

        return new QueryBuilderResult($this->getRwPdo(), $sql, $sqlParams);
    }

    /**
     * 使用 rwPdo($this->getRwPdo()) 来执行 INSERT
     *
     * @param string $table - 表名
     * @param string[] $columns - ['col1', 'col2', ...]
     * @param string[] $uniqueKeys - ['col1', 'col2', ...] , 导致 INSERT 冲突的 UNIQUE KEY, UPDATE 时不更新这些字段
     * @param array $data - object array, [[val11, val12, ...], [val21, val22, ...], ...]
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function insertMultiValueOrUpdate($table, $columns, $uniqueKeys, $data)
    {
        if (empty($uniqueKeys)) {
            return null;
        }
        if (!is_array($uniqueKeys)) {
            throw new \InvalidArgumentException('参数传递错误');
        }
        $uniqueKeys = array_map(function ($item) {
            return trim(strval($item));
        }, $uniqueKeys);

        $qbResult = $this->insertMultiValue($table, $columns, $data);
        if (empty($qbResult)) {
            return null;
        }
        $sql = trim($qbResult->getSql());
        $sqlParams = $qbResult->getSqlParams();

        $oxo = ' ON DUPLICATE KEY UPDATE ';
        foreach ($columns as $column) {
            $column = trim(strval($column));
            if (in_array($column, $uniqueKeys)) {
                continue;
            }
            $sql .= "{$oxo}{$column} = VALUES({$column})";
            $oxo = ', ';
        }

        return new QueryBuilderResult($this->getRwPdo(), $sql, $sqlParams);
    }

    /**
     * 使用 rwPdo($this->getRwPdo()) 来执行 DELETE,
     * 所以在 where 中使用 QueryBuilder::RAW_WHERE 时也要使用 rwPdo->quote 来转义参数字符串($this->getRwPdo()->quote)
     *
     * @param string $table - 表名
     * @param null|array $where
     * @param null|array $orderBy - ['col1'=>'desc', 'col2'=>'asc', ...]
     * @param int $limit
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function delete($table, $where = null, $orderBy = null, $limit = 0)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }

        $sql = "DELETE FROM {$table}";
        $sqlParams = null;
        if ($where) {
            $result = $this->buildWhere($where);
            if ($result) {
                $whereSql = trim($result['where']);
                if ($whereSql) {
                    $sql .= " WHERE {$whereSql}";
                }
                $sqlParams = $result['params'];
            }
        }

        if ($orderBy) {
            if (!is_array($orderBy)) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            $oxo = ' ORDER BY ';
            foreach ($orderBy as $column => $order) {
                $column = trim(strval($column));
                if (empty($column)) {
                    throw new \InvalidArgumentException('参数传递错误');
                }
                $order = strtoupper(trim(strval($order)));
                if (!($order == 'ASC' || $order == 'DESC')) {
                    throw new \InvalidArgumentException('参数传递错误');
                }
                $sql .= "{$oxo}{$column} {$order}";
                $oxo = ', ';
            }
        }

        $limit = intval($limit);
        if ($limit) {
            $sql .= " LIMIT {$limit}";
        }

        return new QueryBuilderResult($this->getRwPdo(), $sql, $sqlParams);
    }

    /**
     * 使用 rwPdo($this->getRwPdo()) 来执行 UPDATE,
     * 所以在 where 中使用 QueryBuilder::RAW_WHERE 时也要使用 rwPdo->quote 来转义参数字符串($this->getRwPdo()->quote)
     *
     * @param string $table - 表名
     * @param array $data - ['col1'=>val1, 'col2'=>val2, ...]
     * @param null|array $where
     * @param null|array $orderBy - ['col1'=>'desc', 'col2'=>'asc', ...]
     * @param int $limit
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function update($table, $data, $where = null, $orderBy = null, $limit = 0)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }
        if (empty($data)) {
            return null;
        }
        if (!is_array($data)) {
            throw new \InvalidArgumentException('参数传递错误');
        }

        $sql = "UPDATE {$table}";
        $sqlParams = array();

        $oxo = ' SET ';
        foreach ($data as $column => $value) {
            $column = trim(strval($column));
            if (empty($column)) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            if (isset($value)) {
                $sql .= "{$oxo}{$column} = ?";
                $sqlParams[] = $value;
            } else {
                $sql .= "{$oxo}{$column} = NULL";
            }
            $oxo = ', ';
        }

        if ($where) {
            $result = $this->buildWhere($where);
            if ($result) {
                $whereSql = trim($result['where']);
                if ($whereSql) {
                    $sql .= " WHERE {$whereSql}";
                }
                if ($result['params']) {
                    $sqlParams = array_merge($sqlParams, $result['params']);
                }
            }
        }

        if ($orderBy) {
            if (!is_array($orderBy)) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            $oxo = ' ORDER BY ';
            foreach ($orderBy as $column => $order) {
                $column = trim(strval($column));
                if (empty($column)) {
                    throw new \InvalidArgumentException('参数传递错误');
                }
                $order = strtoupper(trim(strval($order)));
                if (!($order == 'ASC' || $order == 'DESC')) {
                    throw new \InvalidArgumentException('参数传递错误');
                }
                $sql .= "{$oxo}{$column} {$order}";
                $oxo = ', ';
            }
        }

        $limit = intval($limit);
        if ($limit) {
            $sql .= " LIMIT {$limit}";
        }

        return new QueryBuilderResult($this->getRwPdo(), $sql, $sqlParams);
    }

    /**
     * 使用 rwPdo($this->getRwPdo()) 来执行 UPDATE
     *
     * 基于 id 字段来批量更新表中的数据, 要求表的主键字段名为 id
     *
     * @param string $table - 表名
     * @param string[] $columns - ['col1', 'col2', ...]
     * @param array $data - map[object], [id1=>[val11, val12, ...], id2=>[val21, val22, ...], ...]
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function updateMultiValue($table, $columns, $data)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }
        if (empty($columns)) {
            return null;
        }
        if (empty($data)) {
            return null;
        }

        if (!is_array($columns)) {
            throw new \InvalidArgumentException('参数传递错误');
        }
        if (!is_array($data)) {
            throw new \InvalidArgumentException('参数传递错误');
        }

        $validColumns = array();
        foreach ($columns as $column) {
            $column = trim(strval($column));
            if (empty($column)) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            $validColumns[] = $column;
        }
        $columns = $validColumns;
        $columnsCnt = count($columns);

        $sqlParams = array();
        foreach ($data as $id => $line) {
            if (empty($id) || empty($line) || !is_array($line) || count($line) < $columnsCnt) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            $sqlParams[] = $id;
            $line = array_values($line);
            for ($i = 0; $i < $columnsCnt; $i++) {
                $sqlParams[] = $line[$i];
            }
        }

        $unionSql = 'SELECT ? AS id';
        foreach ($columns as $column) {
            $unionSql .= ", ? AS {$column}";
        }

        $dataCnt = count($data);
        if ($dataCnt > 1) {
            $unionSql = "({$unionSql})";
            $unionSelect = ' UNION ALL (SELECT ' . implode(',', array_fill(0, $columnsCnt + 1, '?')) . ')';
            for ($i = 1; $i < $dataCnt; $i++) {
                $unionSql .= $unionSelect;
            }
        }

        $sql = "UPDATE {$table} AS u JOIN ({$unionSql}) AS t ON u.id = t.id";

        $oxo = ' SET ';
        foreach ($columns as $column) {
            $sql .= "{$oxo}u.{$column} = t.{$column}";
            $oxo = ', ';
        }

        return new QueryBuilderResult($this->getRwPdo(), $sql, $sqlParams);
    }

    /**
     * 使用 rPdo($this->getRPdo()) 来执行 SELECT,
     * 所以在 where 中使用 QueryBuilder::RAW_WHERE 时也要使用 rPdo->quote 来转义参数字符串($this->getRPdo()->quote)
     *
     * @param string $table - 表名
     * @param string|string[] $columns - '*' 或者 'col1,col2,...' 或者 ['col1', 'col2', ...], 支持函数和合集函数, 例如 'SUBSTR(name, 1, 1) AS surname, col2, ...' 或者 ['col1', 'SUM(amount) AS balance', ...]
     * @param null|array $where
     * @param null|string|string[] $groupBy - 'col1,col2,...' 或者 ['col1', 'col2', ...]
     * @param null|array $orderBy - ['col1'=>'desc', 'col2'=>'asc', ...]
     * @param int $limit
     * @param int $offset
     * @return null|QueryBuilderResult
     * @throws \InvalidArgumentException
     */
    public function select($table, $columns, $where = null, $groupBy = null, $orderBy = null, $limit = 0, $offset = 0)
    {
        $table = trim(strval($table));
        if (empty($table)) {
            return null;
        }

        if (empty($columns)) {
            throw new \InvalidArgumentException('参数传递错误');
        }
        $columnString = $this->validColumnString($columns);
        if (empty($columnString)) {
            throw new \InvalidArgumentException('参数传递错误');
        }
        $this->columnString = $columnString;

        $sql = "SELECT {$columnString} FROM {$table}";
        $sqlParams = array();

        if ($where) {
            $result = $this->buildWhere($where);
            if ($result) {
                $whereSql = trim($result['where']);
                if ($whereSql) {
                    $sql .= " WHERE {$whereSql}";
                }
                if ($result['params']) {
                    $sqlParams = array_merge($sqlParams, $result['params']);
                }
            }
        }

        if ($groupBy) {
            if (is_string($groupBy)) {
                $groupBy = trim($groupBy);
                if (empty($groupBy)) {
                    throw new \InvalidArgumentException('参数传递错误');
                }
                $sql .= " GROUP BY {$groupBy}";
            } elseif (is_array($groupBy)) {
                $sql .= ' GROUP BY ' . implode(', ', array_map(function ($column) {
                        return trim(strval($column));
                    }, $groupBy));
            } else {
                throw new \InvalidArgumentException('参数传递错误');
            }
        }

        if ($orderBy) {
            if (!is_array($orderBy)) {
                throw new \InvalidArgumentException('参数传递错误');
            }
            $oxo = ' ORDER BY ';
            foreach ($orderBy as $column => $order) {
                $column = trim(strval($column));
                if (empty($column)) {
                    throw new \InvalidArgumentException('参数传递错误');
                }
                $order = strtoupper(trim(strval($order)));
                if (!($order == 'ASC' || $order == 'DESC')) {
                    throw new \InvalidArgumentException('参数传递错误');
                }
                $sql .= "{$oxo}{$column} {$order}";
                $oxo = ', ';
            }
        }

        $limit = intval($limit);
        if ($limit) {
            $sql .= " LIMIT {$limit} OFFSET " . intval($offset);
        }

        return new QueryBuilderResult($this->getRPdo(), $sql, $sqlParams);
    }

    /**
     * @param array $where
     * @param string $logicalOp - refer self::LOGICAL_AND | self::LOGICAL_OR
     * @return array|null - ['where'=>'whereSql', 'params'=>[...]]
     * @throws \InvalidArgumentException
     */
    private function buildWhere($where, $logicalOp = self::LOGICAL_AND)
    {
        if (empty($where)) {
            return null;
        }
        if (!is_array($where)) {
            throw new \InvalidArgumentException('参数传递错误');
        }

        $whereSql = null;
        $params = array();
        if ($logicalOp == self::LOGICAL_AND) {
            $oxo = ' AND ';
        } elseif ($logicalOp == self::LOGICAL_OR) {
            $oxo = ' OR ';
        } else {
            throw new \InvalidArgumentException('参数传递错误');
        }

        foreach ($where as $name => $value) {
            if ($name == self::LOGICAL_AND || $name == self::LOGICAL_OR) {
                $result = $this->buildWhere($value, $name);
                $subWhereSql = empty($result['where']) ? null : trim($result['where']);
                $subParams = empty($result['params']) ? array() : $result['params'];
                if ($subWhereSql) {
                    if ($name == self::LOGICAL_AND) {
                        if (empty($whereSql)) {
                            $whereSql = $subWhereSql;
                            $params = $subParams;
                        } else {
                            $whereSql .= "{$oxo}{$subWhereSql}";
                            $params = array_merge($params, $subParams);
                        }
                    } else {
                        if (empty($whereSql)) {
                            $whereSql = "({$subWhereSql})";
                            $params = $subParams;
                        } else {
                            $whereSql .= "{$oxo}({$subWhereSql})";
                            $params = array_merge($params, $subParams);
                        }
                    }
                }
            } elseif ($name == self::RAW_WHERE) {
                if (empty($whereSql)) {
                    $whereSql = $value;
                } else {
                    $whereSql .= "{$oxo}{$value}";
                }
            } else {
                if (is_array($value)) {
                    foreach ($value as $op => $val) {
                        $op = strtoupper(trim(strval($op)));
                        if ($op == 'LIKE'
                            || $op == 'NOT LIKE'
                            || $op == 'REGEXP'
                            || $op == 'NOT REGEXP'
                        ) {
                            $val = strval($val);
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} ?";
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} ?";
                            }
                            $params[] = $val;
                        } elseif ($op == 'BETWEEN' || $op == 'NOT BETWEEN') {
                            if (!is_array($val)) {
                                throw new \InvalidArgumentException('参数传递错误');
                            }
                            $val = array_values($val);
                            if (count($val) < 2) {
                                throw new \InvalidArgumentException('参数传递错误');
                            }
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} ? AND ?";
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} ? AND ?";
                            }
                            $params[] = $val[0];
                            $params[] = $val[1];
                        } elseif ($op == 'IN' || $op == 'NOT IN') {
                            if (!is_array($val)) {
                                throw new \InvalidArgumentException('参数传递错误');
                            }
                            $val = array_values($val);
                            $cnt = count($val);
                            if ($cnt < 1) {
                                throw new \InvalidArgumentException('参数传递错误');
                            }
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} (" . implode(',', array_fill(0, $cnt, '?')) . ')';
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} (" . implode(',', array_fill(0, $cnt, '?')) . ')';
                            }
                            $params = array_merge($params, $val);
                        } else {
                            if (empty($whereSql)) {
                                $whereSql = "{$name} {$op} ?";
                            } else {
                                $whereSql .= "{$oxo}{$name} {$op} ?";
                            }
                            $params[] = $val;
                        }
                    }
                } else {
                    $valueStr = strtoupper(trim(strval($value)));
                    if ($valueStr == 'IS NULL') {
                        if (empty($whereSql)) {
                            $whereSql = "{$name} {$valueStr}";
                        } else {
                            $whereSql .= "{$oxo}{$name} {$valueStr}";
                        }
                    } elseif ($valueStr == 'IS NOT NULL') {
                        if (empty($whereSql)) {
                            $whereSql = "{$name} {$valueStr}";
                        } else {
                            $whereSql .= "{$oxo}{$name} {$valueStr}";
                        }
                    } else {
                        if (empty($whereSql)) {
                            $whereSql = "{$name} = ?";
                            $params[] = $value;
                        } else {
                            $whereSql .= "{$oxo}{$name} = ?";
                            $params[] = $value;
                        }
                    }
                }
            }
        }

        return array('where' => $whereSql, 'params' => $params);
    }

}